package scalab.utils

//checked

trait HSQLDB extends Reporter{

	import java.sql.{Connection, Statement, DriverManager, SQLException, ResultSet}

	var connection: Connection = _
	var statement: Statement = _
	//set autocommit to false for performances
	//and control tmp tables (tmp table are emptied on commit)
	final def connect(fileName: String): Boolean = connect(fileName, true)

	final def connect(fileName: String, autoCommit: Boolean): Boolean = {
		try{
			Class.forName("org.hsqldb.jdbcDriver") //load HSQLDB's JDBC Driver
			connection = DriverManager.getConnection("jdbc:hsqldb:file:" + fileName, "sa", "")
			statement = connection.createStatement
			connection setAutoCommit autoCommit
			true
		}
		catch{
			case se: SQLException => 
				fail(se.getMessage)
				false
		}
	}

	final def resultQuery(query: String): ResultSet = resultQuery(query, this)

	final def resultQuery(query: String, reporter: Reporter): ResultSet = try{
		statement.executeQuery(query)
	}
	catch{
		case se: SQLException =>
			reporter.fail("Query error: " + se.getMessage)
			null
	}

	final def createTable(tableName: String, query: String): Boolean = try{
		statement.executeUpdate(query)
		//info("table " + tableName + " has been succesfully created!")
		true
	}
	catch{
		case se: SQLException if(se.getErrorCode == HSQLDB.TABLE_ALREADY_EXISTS) => false
	}

	final def disconnect{
		try{
			statement.execute("SHUTDOWN")
			connection.close()
		}
		catch{
			case se: SQLException =>
		}
		finally{
			statement = null;
			connection = null;
		}
	}

	final def commit = connection.commit
}

object HSQLDB{
	lazy val TABLE_ALREADY_EXISTS      : Int = -21
	lazy val UNIQUE_CONSTRAINT_VIOLATED: Int = -104
}
